﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace GiangDay
{
    class FunctionLinh
    {
        private DB database = new DB();
        private SqlDataAdapter da;
        private SqlCommand cmd;

        #region FrGiaoVien    
        public DataTable laydanhsachgiaovien()
        {
            database.Open();
            da = new SqlDataAdapter("SELECT MA_GV, TEN_GV, SDT_GV, NGAY_SINH_GV, DIA_CHI_GV,GIOI_TINH, CASE GIOI_TINH WHEN 1 THEN 'Nam' WHEN 0 THEN N'Nữ' END AS DGIOI_TINH,USERNAME, PASSWORD, QUYEN, CASE QUYEN WHEN 1 THEN N'Quản lý' WHEN 0 THEN N'Giảng viên' END AS DQUYEN,TEN_HV,TEN_CV FROM GIAO_VIEN,CHUC_VU,HOC_VI WHERE GIAO_VIEN.MA_HV=HOC_VI.MA_HV AND GIAO_VIEN.MA_CV=CHUC_VU.MA_CV", database.conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            database.Close();
            return dt;
        }
        public bool themGV(string magv,string macv,string mahv, string tengv, string sdt, string diachi, DateTime ngaysinh,int gioitinh,string user, string pass, int quyen)
        {
                database.Open();
                string them = "insert into GIAO_VIEN values('" + magv + "','"+macv+"','"+mahv+"', N'" + tengv + "','" + sdt + "', N'" + diachi + "','" + ngaysinh + "',N'"+gioitinh+"','" + user + "','" + pass + "','" + quyen + "')";
                cmd=new SqlCommand(them, database.conn);
                cmd.ExecuteNonQuery();
                MessageBox.Show("Thêm Giáo Viên thành công!!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
                database.Close();
                return true;
        }
        public bool suaGV(string magv,string macv,string mahv, string tengv, string sdt, string diachi, DateTime ngaysinh,int gioitinh,string user, string pass, int quyen)
        {
            database.Open();
            string sua = "update GIAO_VIEN set MA_CV='"+macv+"',MA_HV='"+mahv+"',TEN_GV=N'" + tengv + "',SDT_GV='" + sdt + "',DIA_CHI_GV=N'" + diachi + "',NGAY_SINH_GV='" + ngaysinh + "',GIOI_TINH=N'"+gioitinh+"', USERNAME='"+ user +"',PASSWORD='"+pass+"',QUYEN='"+ quyen+"' where MA_GV='" + magv + "'";
            cmd = new SqlCommand(sua, database.conn);
            cmd.ExecuteNonQuery();
            MessageBox.Show("Cập nhật Giáo Viên thành công!!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
            database.Close();
            return true;
        }
        public bool xoaGV(string magv)
        {
            database.Open();
            string xoa = "delete GIAO_VIEN where MA_GV='" + magv + "'";
            cmd = new SqlCommand(xoa, database.conn);
            cmd.ExecuteNonQuery();
            MessageBox.Show("Xóa Giáo Viên thành công!!", "Thông báo", MessageBoxButtons.OK,MessageBoxIcon.Information);
            database.Close();
            return true;
        }
        #endregion

        #region FrChucVu
        public DataTable loadChucVu()
        {
            da = new SqlDataAdapter("SELECT * FROM CHUC_VU", database.conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
        public bool themCV(string macv, string tencv)
        {
            database.Open();
            string them = "insert into CHUC_VU values('" + macv + "', N'" + tencv + "')";
            cmd = new SqlCommand(them, database.conn);
            cmd.ExecuteNonQuery();
            MessageBox.Show("Thêm Chức vụ thành công!!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
            database.Close();
            return true;
        }
        public bool suaCV(string macv, string tencv)
        {
            database.Open();
            string them = "update CHUC_VU set TEN_CV=N'" + tencv + "' where MA_CV='" + macv + "'";
            cmd = new SqlCommand(them, database.conn);
            cmd.ExecuteNonQuery();
            MessageBox.Show("Cập nhật Chức vụ thành công!!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
            database.Close();
            return true;
        }
        public bool xoaCV(string macv)
        {
            database.Open();
            string xoa = "delete CHUC_VU where MA_CV='" + macv + "'";
            cmd = new SqlCommand(xoa, database.conn);
            cmd.ExecuteNonQuery();
            MessageBox.Show("Xóa Chức vụ thành công!!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
            database.Close();
            return true;
        }

        #endregion

        #region FrCoVan
        public DataTable loadCV()
        {
            da = new SqlDataAdapter("select DISTINCT TEN_GV,TEN_LOP,TEN_KHOA,TEN_HK from CO_VAN cv, GIAO_VIEN gv, LOP lp, KHOA kh, HOC_KY hk where cv.MA_GV=gv.MA_GV and cv.MA_LOP=lp.MA_LOP and cv.MA_KHOA=kh.MA_KHOA and cv.MA_HK=hk.MA_HK", database.conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
        public bool themCoVan(string magv, string malop, string makhoa, string mahk)
        {
            database.Open();
            string them = "insert into CO_VAN values('" + magv + "','" + malop + "','" + makhoa + "','" + mahk + "')";
            cmd = new SqlCommand(them, database.conn);
            cmd.ExecuteNonQuery();
            database.Close();
            return true;
        }

        public bool suaCoVan(string magv, string malop, string makhoa, string mahk)
        {
            database.Open();
            string sua = "update CO_VAN set MA_LOP='" + malop + "',MA_KHOA='" + makhoa + "',MA_HK='" + mahk + "' where MA_GV='" + magv + "'";
            cmd = new SqlCommand(sua, database.conn);
            cmd.ExecuteNonQuery();
            database.Close();
            return true;
        }

        public bool xoaCoVan(string magv)
        {
            database.Open();
            string xoa = "delete CO_VAN where MA_GV='" + magv + "'";
            cmd = new SqlCommand(xoa, database.conn);
            cmd.ExecuteNonQuery();
            database.Close();
            return true;
        }
        #endregion

        #region FrLop
        public DataTable loadLop()
        {
            da = new SqlDataAdapter("SELECT MA_LOP,TEN_LOP, SI_SO, TEN_HE,TEN_KHOA FROM LOP,KHOA,HE_DAO_TAO WHERE LOP.MA_HE=HE_DAO_TAO.MA_HE AND LOP.MA_KHOA=KHOA.MA_KHOA", database.conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
        public bool themLop(string malop, string mahe, string makhoa, string tenlop, int siso)
        {
            database.Open();
            string them = "insert into LOP values('" + malop + "','" + mahe + "','" + makhoa + "',N'" + tenlop + "','" + siso + "')";
            cmd = new SqlCommand(them, database.conn);
            cmd.ExecuteNonQuery();
            database.Close();
            return true;
        }

        public bool suaLop(string malop, string mahe, string makhoa, string tenlop, int siso)
        {
            database.Open();
            string sua = "update LOP set MA_HE='" + mahe + "',MA_KHOA='" + makhoa + "',TEN_LOP=N'" + tenlop + "',SI_SO='" + siso + "' where MA_LOP='" + malop + "'";
            cmd = new SqlCommand(sua, database.conn);
            cmd.ExecuteNonQuery();
            database.Close();
            return true;
        }

        public bool xoaLop(string malop)
        {
            database.Open();
            string xoa = "delete LOP where MA_LOP='" + malop + "')";
            cmd = new SqlCommand(xoa, database.conn);
            cmd.ExecuteNonQuery();
            database.Close();
            return true;
        }

        #endregion

        #region FrHocVi
        public DataTable loadhocvi()
        {
            da = new SqlDataAdapter("SELECT * FROM HOC_VI", database.conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
        public bool themHV(string mahv, string tenhv, float luong)
        {
            database.Open();
            string them = "insert into HOC_VI values('" + mahv + "', N'" + tenhv + "', '"+luong+"')";
            cmd = new SqlCommand(them, database.conn);
            cmd.ExecuteNonQuery();
            MessageBox.Show("Thêm Học vị thành công!!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
            database.Close();
            return true;
        }
        public bool suaHV(string mahv,string tenhv, float luong)
        {
            database.Open();
            string them = "update HOC_VI set TEN_HV=N'" + tenhv + "', LUONG1TIET='"+luong+"' where MA_HV='"+mahv+"'";
            cmd = new SqlCommand(them, database.conn);
            cmd.ExecuteNonQuery();
            MessageBox.Show("Cập nhật Học vị thành công!!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
            database.Close();
            return true;
        }
        public bool xoaHV(string mahv)
        {
            database.Open();
            string xoa = "delete HOC_VI where MA_HV='" + mahv + "'";
            cmd = new SqlCommand(xoa, database.conn);
            cmd.ExecuteNonQuery();
            MessageBox.Show("Xóa Học vị thành công!!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
            database.Close();
            return true;
        }

        #endregion

        #region FrKhoa
        public DataTable loadkhoa()
        {
            da = new SqlDataAdapter("SELECT * FROM KHOA ORDER BY MA_KHOA ASC", database.conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
        public int loadKhoaHT()
        {
            da = new SqlDataAdapter("SELECT MA_KHOA FROM KHOA ORDER BY MA_KHOA DESC", database.conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            if (dt.Rows.Count == 0) return 0;
            return Convert.ToInt32(dt.Rows[0]["MA_KHOA"].ToString().Substring(1, dt.Rows[0]["MA_KHOA"].ToString().Length - 1));
            
        }
        public int loadNamHT()
        {
            da = new SqlDataAdapter("SELECT NAM_HOC FROM KHOA ORDER BY NAM_HOC DESC", database.conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            DateTime saveNow = DateTime.Now;
            if (dt.Rows.Count == 0) return saveNow.Year;
            return Convert.ToInt32(dt.Rows[0]["NAM_HOC"].ToString().Substring(6, 5));

        }
        public bool addKhoa(int  makhoa, string nHoc)
        {
            database.Open();
            string mKhoa = "K" + makhoa.ToString();
            string tKhoa = "Khóa " + makhoa.ToString();           
            string them = "insert into KHOA(MA_KHOA, TEN_KHOA, NAM_HOC) values('" + mKhoa + "', N'" + tKhoa + "', '" + nHoc + "') ;" +
                          "insert into HOC_KY(MA_HK, MA_KHOA, TEN_HK) values ('HK1', '" + mKhoa + "', N'Học kỳ I') ;" +
                          "insert into HOC_KY(MA_HK, MA_KHOA, TEN_HK) values ('HK2', '" + mKhoa + "', N'Học kỳ II') ;";                         
            cmd = new SqlCommand(them, database.conn);
            cmd.ExecuteNonQuery();           
            database.Close();
            return true;
        }
        public bool suaKHOA(string makhoa, string tenkhoa, int namhoc)
        {
            database.Open();
            string them = "update KHOA set TEN_KHOA=N'" + tenkhoa + "',NAM_HOC='"+namhoc+"' where MA_KHOA='" + makhoa + "'";
            cmd = new SqlCommand(them, database.conn);
            cmd.ExecuteNonQuery();
            MessageBox.Show("Cập nhật Khoa thành công!!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
            database.Close();
            return true;
        }
        public bool delKhoa(string makhoa)
        {
            database.Open();
            string xoa = "delete from KHOA where MA_KHOA='" + makhoa + "' ; DELETE FROM HOC_KY WHERE MA_KHOA='"+ makhoa+"'";
            cmd = new SqlCommand(xoa, database.conn);
            cmd.ExecuteNonQuery();            
            database.Close();
            return true;
        }

        #endregion

        #region FrMonHoc
        public DataTable loadMonHoc()
        {
            da = new SqlDataAdapter("SELECT  KQ1.MA_MON, TEN_MON, SO_TC, LT, TH, (ISNULL(LT, 0)+ISNULL(TH, 0)) AS SUMST  FROM (SELECT MON_HOC.MA_MON, TEN_MON, SO_TC, LT  FROM MON_HOC LEFT JOIN (SELECT SO_TIET AS LT, MA_MON FROM HOC_PHAN WHERE MA_LMH='LT') AS LT ON MON_HOC.MA_MON=LT.MA_MON)  AS KQ1 LEFT JOIN (SELECT SO_TIET AS TH, MA_MON FROM HOC_PHAN WHERE MA_LMH='TH') AS TH ON KQ1.MA_MON=TH.MA_MON", database.conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }      
        public DataTable timMonHoc(int loai, string text)//0 Mã,
        {
            string query = "";
            if (loai==1)           
                query="KQ1.MA_MON LIKE '%"+ text +"%'";
            else query="TEN_MON LIKE N'%"+ text +"%'";  
            da = new SqlDataAdapter("SELECT  KQ1.MA_MON, TEN_MON, SO_TC, LT, TH FROM (SELECT MON_HOC.MA_MON, TEN_MON, SO_TC, LT  FROM MON_HOC LEFT JOIN (SELECT SO_TIET AS LT, MA_MON FROM HOC_PHAN WHERE MA_LMH='LT') AS LT ON MON_HOC.MA_MON=LT.MA_MON)  AS KQ1 LEFT JOIN (SELECT SO_TIET AS TH, MA_MON FROM HOC_PHAN WHERE MA_LMH='TH') AS TH ON KQ1.MA_MON=TH.MA_MON WHERE "+query, database.conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
        public bool themMonHoc(string mamonhoc, string tenmonhoc, int soTc)
        {
            database.Open();
            string them = "insert into MON_HOC values('" + mamonhoc + "', N'" + tenmonhoc + "', " + soTc + ")";
            cmd = new SqlCommand(them, database.conn);
            try
            {
                cmd.ExecuteNonQuery();        
            }
            catch (Exception)
            {
                database.Close();
                return false;
            }                              
            database.Close();
            return true;
        }
        public bool themLythuyet(string maMon, string maLoaimon, int soTiet) 
        {
            database.Open();
            string them = "insert into HOC_PHAN values('" + maMon + "', '" + maLoaimon + "', " + soTiet + ")";
            cmd = new SqlCommand(them, database.conn);
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                database.Close();
                return false;
            }
            database.Close();
            return true;
        }
        public bool themThucanh(string maMon, string maLoaimon, int soTiet)
        {
            database.Open();
            string them = "insert into HOC_PHAN values('" + maMon + "', '" + maLoaimon + "', " + soTiet + ")";
            cmd = new SqlCommand(them, database.conn);
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                database.Close();
                return false;
            }
            database.Close();
            return true;
        }
        public bool suaMonHoc(string mamonhoc, string tenmonhoc, int soTC)
        {
            database.Open();
            string them = "update MON_HOC set TEN_MON=N'" + tenmonhoc + "', SO_TC="+ soTC +" where MA_MON='" + mamonhoc + "' ; delete from HOC_PHAN where MA_MON='"+ mamonhoc +"'";
            cmd = new SqlCommand(them, database.conn);
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                database.Close();
                return false;
            }
            database.Close();
            return true;
        }
        public bool xoaMonHoc(string mamonhoc)
        {
            database.Open();
            string xoa = "delete from MON_HOC where MA_MON='" + mamonhoc + "' ; delete from HOC_PHAN where MA_MON='" + mamonhoc + "'";
            cmd = new SqlCommand(xoa, database.conn);
            cmd.ExecuteNonQuery();          
            database.Close();
            return true;
        }

        #endregion

        #region FrHeDaoTao
        public DataTable loadHDT()
        {
            da = new SqlDataAdapter("SELECT * FROM HE_DAO_TAO", database.conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
        public bool themHDT(string mahdt, string tenhdt, int namdt, int order)
        {
            database.Open();
            string them = "insert into HE_DAO_TAO values('" + mahdt + "', N'" + tenhdt + "','" + namdt + "', '"+ order+"')";
            cmd = new SqlCommand(them, database.conn);
            cmd.ExecuteNonQuery();
            MessageBox.Show("Thêm Hệ Đào Tạo thành công!!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
            database.Close();
            return true;
        }
        public bool suaHDT(string mahdt, string tenhdt, int namdt, int order)
        {
            database.Open();
            string them = "update HE_DAO_TAO set TEN_HE=N'" + tenhdt + "',NAM_HE='" + namdt + "', ORDER_HE='"+order+"' where MA_HE='" + mahdt + "'";
            cmd = new SqlCommand(them, database.conn);
            cmd.ExecuteNonQuery();
            MessageBox.Show("Cập nhật Hệ Đào Tạo thành công!!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
            database.Close();
            return true;
        }
        public bool xoaHDT(string mahdt)
        {
            database.Open();
            string xoa = "delete HE_DAO_TAO where MA_HE='" + mahdt + "'";
            cmd = new SqlCommand(xoa, database.conn);
            cmd.ExecuteNonQuery();
            MessageBox.Show("Xóa Hệ Đào Tạo thành công!!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
            database.Close();
            return true;
        }

        #endregion

        #region FrHocKy
        public DataTable loadhocky()
        {
            da = new SqlDataAdapter("SELECT MA_HK, TEN_HK, TEN_KHOA FROM HOC_KY LEFT JOIN KHOA ON HOC_KY.MA_KHOA=KHOA.MA_KHOA ", database.conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
        public bool themHK(string mahk, string makhoa, string tenhk)
        {
            database.Open();
            string them = "insert into HOC_KY values('" + mahk + "', '" + makhoa + "',N'" + tenhk + "')";
            cmd = new SqlCommand(them, database.conn);
            cmd.ExecuteNonQuery();
            MessageBox.Show("Thêm Học kỳ thành công!!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
            database.Close();
            return true;
        }
        public bool suaHK(string mahk,  string makhoa ,string tenhk)
        {
            database.Open();
            string them = "update HOC_KY set  MA_KHOA='" + makhoa + "',TEN_HK=N'" + tenhk + "'where MA_HK='" + mahk + "'";
            cmd = new SqlCommand(them, database.conn);
            cmd.ExecuteNonQuery();
            MessageBox.Show("Cập nhật Học kỳ thành công!!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
            database.Close();
            return true;
        }
        public bool xoaHK(string mahk)
        {
            database.Open();
            string xoa = "delete HOC_KY where MA_HK='" + mahk + "'";
            cmd = new SqlCommand(xoa, database.conn);
            cmd.ExecuteNonQuery();
            MessageBox.Show("Xóa Học kỳ thành công!!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
            database.Close();
            return true;
        }

        #endregion

        #region FrLoaiMonHoc
        public DataTable loadLoaiMH()
        {
            da = new SqlDataAdapter("SELECT * FROM LOAI_MH", database.conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
        public bool themLoaiMH(string maloai, int sotiet, int heso)
        {
            database.Open();
            string them = "insert into LOAI_MH values('" + maloai + "', '" + sotiet + "','" + heso + "')";
            cmd = new SqlCommand(them, database.conn);
            cmd.ExecuteNonQuery();
            MessageBox.Show("Thêm Loại Môn Học thành công!!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
            database.Close();
            return true;
        }
        public bool suaLoaiMH(string maloai, int sotiet, int heso)
        {
            database.Open();
            string them = "update LOAI_MH set SO_TIET_MH=N'" + sotiet + "',HE_SO='" + heso + "' where MA_LMH='" + maloai + "'";
            cmd = new SqlCommand(them, database.conn);
            cmd.ExecuteNonQuery();
            MessageBox.Show("Cập nhật Loại Môn Học thành công!!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
            database.Close();
            return true;
        }
        public bool xoaLoaiMH(string maloai)
        {
            database.Open();
            string xoa = "delete LOAI_MH where MA_LMH='" + maloai + "'";
            cmd = new SqlCommand(xoa, database.conn);
            cmd.ExecuteNonQuery();
            MessageBox.Show("Xóa Loại Môn Học thành công!!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
            database.Close();
            return true;
        }

        #endregion

    }
}